package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Order;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.persistence.LineItemDao;
import org.csu.mypetstore.persistence.OrderDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class OrderDaoImpl implements OrderDao {
    private static final String GET_ORDER_BY_USERNAME = "select * from orders where userid = ?";
    private static final String GET_ORDER = "select * from orders where orderid = ?";
    private static final String INSERT_ORDER = "insert into orders values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String INSERT_ORDER_STATUS = "insert into orderstatus values(?,?,?,?)";

    @Override
    public List<Order> getOrdersByUsername(String username) {
        List<Order> orders = new ArrayList<>();

        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDER_BY_USERNAME);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                Order order = new Order();
                setOrderInfo(resultSet, order);
                //还需要初始化状态和条目，待定
                LineItemDao lineItemDao = new LineItemDaoImpl();
                order.setLineItems(lineItemDao.getLineItemsByOrderId(order.getOrderId()));
                orders.add(order);
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return orders;
    }

    @Override
    public Order getOrder(int orderId) {
        Order order = null;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDER);
            preparedStatement.setInt(1, orderId);
            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                order = new Order();
                setOrderInfo(resultSet, order);

                LineItemDao lineItemDao = new LineItemDaoImpl();
                order.setLineItems(lineItemDao.getLineItemsByOrderId(orderId));
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return order;
    }

    @Override
    public void insertOrder(Order order) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ORDER);
            preparedStatement.setInt(1,order.getOrderId());
            preparedStatement.setString(2,order.getUsername());
            preparedStatement.setDate(3, (java.sql.Date) order.getOrderDate());
            preparedStatement.setString(4,order.getShipAddress1());
            preparedStatement.setString(5,order.getShipAddress2());
            preparedStatement.setString(6,order.getShipCity());
            preparedStatement.setString(7,order.getShipState());
            preparedStatement.setString(8,order.getShipZip());
            preparedStatement.setString(9,order.getShipCountry());
            preparedStatement.setString(10,order.getBillAddress1());
            preparedStatement.setString(11,order.getBillAddress2());
            preparedStatement.setString(12,order.getBillCity());
            preparedStatement.setString(13,order.getBillState());
            preparedStatement.setString(14,order.getBillZip());
            preparedStatement.setString(15,order.getBillCountry());
            preparedStatement.setString(16,order.getCourier());
            preparedStatement.setBigDecimal(17,order.getTotalPrice());
            preparedStatement.setString(18,order.getBillToFirstName());
            preparedStatement.setString(19,order.getBillToLastName());
            preparedStatement.setString(20,order.getShipToFirstName());
            preparedStatement.setString(21,order.getShipToLastName());
            preparedStatement.setString(22,order.getCreditCard());
            preparedStatement.setString(23,order.getExpiryDate());
            preparedStatement.setString(24,order.getCardType());
            preparedStatement.setString(25,order.getLocale());
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void insertOrderStatus(Order order) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ORDER_STATUS);
            preparedStatement.setInt(1, order.getOrderId());
            preparedStatement.setInt(2, (int)(Math.random()*100000));//好像是个没什么用的变量
            preparedStatement.setDate(3, new java.sql.Date(new Date().getTime()));
            preparedStatement.setString(4, order.getStatus());
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private void setOrderInfo(ResultSet resultSet, Order order) throws SQLException {//根据resultSet初始化order信息
        order.setOrderId(resultSet.getInt(1));
        order.setUsername(resultSet.getString(2));
        order.setOrderDate(resultSet.getDate(3));
        order.setShipAddress1(resultSet.getString(4));
        order.setShipAddress2(resultSet.getString(5));
        order.setShipCity(resultSet.getString(6));
        order.setShipState(resultSet.getString(7));
        order.setShipZip(resultSet.getString(8));
        order.setShipCountry(resultSet.getString(9));
        order.setBillAddress1(resultSet.getString(10));
        order.setBillAddress2(resultSet.getString(11));
        order.setBillCity(resultSet.getString(12));
        order.setBillState(resultSet.getString(13));
        order.setBillZip(resultSet.getString(14));
        order.setBillCountry(resultSet.getString(15));
        order.setCourier(resultSet.getString(16));
        order.setTotalPrice(resultSet.getBigDecimal(17));
        order.setBillToFirstName(resultSet.getString(18));
        order.setBillToLastName(resultSet.getString(19));
        order.setShipToFirstName(resultSet.getString(20));
        order.setShipToLastName(resultSet.getString(21));
        order.setCreditCard(resultSet.getString(22));
        order.setExpiryDate(resultSet.getString(23));
        order.setCardType(resultSet.getString(24));
        order.setLocale(resultSet.getString(25));
    }
}
